package com.ncs.base.common.rule;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ncs.base.demoExel.entity.Indicator;

public class ExcelUtils {
    // 目录sheet 是否超限列位置数
    private static final int ROOT_SHEET_CELL = 3 ;

    /**
     * 对所有Sheet页加入日期
     * @param path
     * @return
     * @throws IOException
     */
    public static boolean  addHeaderDate(String path) throws IOException {
        boolean flag = false ;
        InputStream fis = null ;
        FileOutputStream fos = null ;

        try{
            fis = new FileInputStream(path);

            //1.创建一个工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            Sheet sheet1 = null ;

            for (Indicator indicator : Indicator.values()) {
                int sheetIndex = indicator.getIndex() ;
                int stratCell = indicator.getStartCell() ;
                int dayNum = indicator.getDayNum() ;

                sheet1 = workbook.getSheetAt(sheetIndex) ;

                while(dayNum < 0){
                    sheet1.getRow(1).getCell(stratCell++).setCellValue(DateUtils.previousDay(dayNum++));
                }
            }

            fos = new FileOutputStream(path);
            workbook.write(fos);

            flag = true ;

        }finally {

            if(fis != null){
                fis.close();
            }

            if(fos != null){
                fos.close();
            }
        }

        return flag ;
    }

    /**
     * 为 Sheet 内加入内容
     * @param path
     * @param sheetIndex
     * @param stratRow
     * @param list
     * @return
     * @throws IOException
     */
    public static boolean  addExcelSheetContent(String path,int sheetIndex,int stratRow, List<List<Object>> list) throws IOException {
        boolean flag = false ;
        InputStream fis = null ;
        FileOutputStream fos = null ;

        try{
            fis = new FileInputStream(path);

            //1.创建一个工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            // 在目录Sheet中标明超限指标
            Sheet sheet0 = workbook.getSheetAt(0) ;
            sheet0.getRow(sheetIndex).getCell(ROOT_SHEET_CELL).setCellValue("是");
            sheet0.getRow(sheetIndex).getCell(ROOT_SHEET_CELL).setCellStyle(buildRootSheetCellStyle(workbook));

            Sheet sheet1 = workbook.getSheetAt(sheetIndex) ;
            Row row = null ;
            Cell cell = null ;

            XSSFCellStyle contentStyle =  buildContentStyle(workbook) ;

            for(List listObject : list){

                row = sheet1.createRow(stratRow);
                //sheet1.getRow(0).getCell() ;

                int i = 0 ;

                for(Object obj : listObject){

                    cell = row.createCell(i);
                    cell.setCellValue(obj.toString()) ;
                    cell.setCellStyle(contentStyle);

                    i++ ;

                }
                stratRow++ ;
            }

            fos = new FileOutputStream(path);
            workbook.write(fos);

            flag = true ;

        }finally {

            if(fis != null){
                fis.close();
            }

            if(fos != null){
                fos.close();
            }
        }

        return flag ;
    }

    public static XSSFCellStyle buildContentStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); //横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); //竖向居中
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //字体样式
        XSSFFont font = workbook.createFont();
        //font.setBold(true); //加粗
        font.setColor(Font.COLOR_NORMAL);//黑色
        font.setFontName("黑体");
        font.setFontHeight(12); //字体大小
        style.setFont(font);
        return style;
    }

    public static XSSFCellStyle buildRootSheetCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); //横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); //竖向居中
        style.setFillForegroundColor(IndexedColors.RED.getIndex());//设置背景色
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //字体样式
        XSSFFont font = workbook.createFont();
        //font.setBold(true); //加粗
        font.setColor(Font.COLOR_NORMAL);//黑色
        font.setFontName("黑体");
        font.setFontHeight(12); //字体大小
        style.setFont(font);
        return style;
    }

    /**
     * 刷入表头日期
     * @param path
     * @param sheetIndex sheet的索引 可从Indicator枚举类中查找
     * @param stratCell  列的开始位置 按索引值
     * @param dayNum     -3 or -8
     * @return
     * @throws IOException
     */
    public static boolean  addHeaderDate(String path,int sheetIndex,int stratCell, int dayNum) throws IOException {
        boolean flag = false ;
        InputStream fis = null ;
        FileOutputStream fos = null ;

        try{
            fis = new FileInputStream(path);

            //1.创建一个工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            Sheet sheet1 = workbook.getSheetAt(sheetIndex) ;

            while(dayNum < 0){
                sheet1.getRow(1).getCell(stratCell++).setCellValue(DateUtils.previousDay(dayNum++));
            }

            fos = new FileOutputStream(path);
            workbook.write(fos);

            flag = true ;

        }finally {

            if(fis != null){
                fis.close();
            }

            if(fos != null){
                fos.close();
            }
        }

        return flag ;
    }

}
